<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>会计自动分录表格</title>
    <style>
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background-color: #f4f4f9;
            margin: 0;
            padding: 20px;
        }

        h2 {
            text-align: center;
            color: #333;
            margin-bottom: 20px;
        }

        .input-container {
            background-color: #fff;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            margin-bottom: 20px;
            display: flex;
            flex-wrap: wrap;
            justify-content: center;
            gap: 10px;
        }

        .input-container label {
            font-weight: bold;
        }

        .input-container input {
            padding: 8px;
            border: 1px solid #ccc;
            border-radius: 4px;
            outline: none;
        }

        .input-container input:focus {
            border-color: #007BFF;
        }

        .input-container button {
            padding: 8px 15px;
            background-color: #007BFF;
            color: #fff;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            transition: background-color 0.3s ease;
        }

        .input-container button:hover {
            background-color: #0056b3;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            background-color: #fff;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
        }

        th,
        td {
            border: 1px solid #ddd;
            padding: 12px;
            text-align: left;
        }

        th {
            background-color: #f2f2f2;
            font-weight: bold;
        }

        tr:nth-child(even) {
            background-color: #f9f9f9;
        }

        tr:hover {
            background-color: #e9f6ff;
        }

        .balance-info {
            margin-top: 20px;
            font-weight: bold;
            text-align: center;
        }

       .balanced {
            color: #28a745;
        }

       .unbalanced {
            color: #dc3545;
        }

        /* 动画效果 */
        @keyframes fadeIn {
            from {
                opacity: 0;
            }
            to {
                opacity: 1;
            }
        }

        table,
       .input-container {
            animation: fadeIn 0.5s ease;
        }

       .debit-credit-group {
            display: flex;
            flex-basis: 100%;
            justify-content: center;
            gap: 10px;
        }
    </style>
    <!-- 引入 xlsx 库 -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
</head>

<body>
    <h2>会计自动分录表格</h2>
    <div class="input-container">
        <label for="date">日期:</label>
        <input type="date" id="date">
        <label for="voucher">凭证号:</label>
        <input type="text" id="voucher">
        <label for="summary">摘要:</label>
        <input type="text" id="summary">
        <div class="debit-credit-group">
            <label for="debit-account">借方科目:</label>
            <input type="text" id="debit-account">
            <label for="debit-amount">借方金额:</label>
            <input type="number" id="debit-amount">
        </div>
        <div class="debit-credit-group">
            <label for="credit-account">贷方科目:</label>
            <input type="text" id="credit-account">
            <label for="credit-amount">贷方金额:</label>
            <input type="number" id="credit-amount">
        </div>
        <button onclick="addEntry()">添加分录</button>
        <button onclick="exportToCSV()">导出为 CSV</button>
        <button onclick="importFromLocalStorage()">从本地存储导入数据</button>
        <input type="file" id="file-input" accept=".csv,.xlsx">
        <button onclick="importFile()">导入文件</button>
    </div>
    <table id="entry-table">
        <thead>
            <tr>
                <th>序号</th>
                <th>日期</th>
                <th>凭证号</th>
                <th>摘要</th>
                <th>会计科目</th>
                <th>借方金额</th>
                <th>贷方金额</th>
                <th>操作</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
    <div id="balance-info" class="balance-info"></div>
    <script>
        const table = document.getElementById('entry-table');
        const balanceInfo = document.getElementById('balance-info');

        // 从本地存储加载数据
        function loadData() {
            const storedData = localStorage.getItem('accountingEntries');
            if (storedData) {
                const entries = JSON.parse(storedData);
                const fragment = document.createDocumentFragment();
                entries.forEach((entry) => {
                    const debitRow = document.createElement('tr');
                    const debitCell1 = debitRow.insertCell(0);
                    const debitCell2 = debitRow.insertCell(1);
                    const debitCell3 = debitRow.insertCell(2);
                    const debitCell4 = debitRow.insertCell(3);
                    const debitCell5 = debitRow.insertCell(4);
                    const debitCell6 = debitRow.insertCell(5);
                    const debitCell7 = debitRow.insertCell(6);
                    const debitCell8 = debitRow.insertCell(7);

                    debitCell1.textContent = entry.index;
                    debitCell2.textContent = entry.date;
                    debitCell3.textContent = padVoucher(entry.voucher);
                    debitCell4.textContent = entry.summary;
                    debitCell5.textContent = entry.debitAccount;
                    debitCell6.textContent = parseFloat(entry.debitAmount).toFixed(2);
                    debitCell7.textContent = '';
                    debitCell8.innerHTML = '<button onclick="editEntry(this)">修改</button><button onclick="deleteEntry(this)">删除</button>';

                    const creditRow = document.createElement('tr');
                    const creditCell1 = creditRow.insertCell(0);
                    const creditCell2 = creditRow.insertCell(1);
                    const creditCell3 = creditRow.insertCell(2);
                    const creditCell4 = creditRow.insertCell(3);
                    const creditCell5 = creditRow.insertCell(4);
                    const creditCell6 = creditRow.insertCell(5);
                    const creditCell7 = creditRow.insertCell(6);
                    const creditCell8 = creditRow.insertCell(7);

                    creditCell1.textContent = '';
                    creditCell2.textContent = '';
                    creditCell3.textContent = '';
                    creditCell4.textContent = '';
                    creditCell5.textContent = entry.creditAccount;
                    creditCell6.textContent = '';
                    creditCell7.textContent = parseFloat(entry.creditAmount).toFixed(2);
                    creditCell8.textContent = '';

                    fragment.appendChild(debitRow);
                    fragment.appendChild(creditRow);
                });
                table.tBodies[0].appendChild(fragment);
                calculateBalance();
            }
        }

        // 保存数据到本地存储
        function saveData() {
            const rows = table.getElementsByTagName('tr');
            const entries = [];
            for (let i = 1; i < rows.length; i += 2) {
                const debitRow = rows[i];
                const creditRow = rows[i + 1];
                const entry = {
                    index: debitRow.cells[0].textContent,
                    date: debitRow.cells[1].textContent,
                    voucher: debitRow.cells[2].textContent,
                    summary: debitRow.cells[3].textContent,
                    debitAccount: debitRow.cells[4].textContent,
                    debitAmount: debitRow.cells[5].textContent,
                    creditAccount: creditRow.cells[4].textContent,
                    creditAmount: creditRow.cells[6].textContent
                };
                entries.push(entry);
            }
            localStorage.setItem('accountingEntries', JSON.stringify(entries));
        }

        function addEntry() {
            const date = document.getElementById('date').value;
            const voucher = document.getElementById('voucher').value;
            const summary = document.getElementById('summary').value;
            const debitAccount = document.getElementById('debit-account').value;
            const debitAmount = parseFloat(document.getElementById('debit-amount').value).toFixed(2);
            const creditAccount = document.getElementById('credit-account').value;
            const creditAmount = parseFloat(document.getElementById('credit-amount').value).toFixed(2);

            const rows = table.getElementsByTagName('tr');
            let newIndex = 1;
            if (rows.length > 1) {
                for (let i = rows.length - 1; i > 0; i--) {
                    if (rows[i].cells[0].textContent) {
                        newIndex = parseInt(rows[i].cells[0].textContent) + 1;
                        break;
                    }
                }
            }

            const debitRow = table.insertRow();
            const debitCell1 = debitRow.insertCell(0);
            const debitCell2 = debitRow.insertCell(1);
            const debitCell3 = debitRow.insertCell(2);
            const debitCell4 = debitRow.insertCell(3);
            const debitCell5 = debitRow.insertCell(4);
            const debitCell6 = debitRow.insertCell(5);
            const debitCell7 = debitRow.insertCell(6);
            const debitCell8 = debitRow.insertCell(7);

            debitCell1.textContent = newIndex;
            debitCell2.textContent = date;
            debitCell3.textContent = padVoucher(voucher);
            debitCell4.textContent = summary;
            debitCell5.textContent = debitAccount;
            debitCell6.textContent = debitAmount;
            debitCell7.textContent = '';
            debitCell8.innerHTML = '<button onclick="editEntry(this)">修改</button><button onclick="deleteEntry(this)">删除</button>';

            const creditRow = table.insertRow();
            const creditCell1 = creditRow.insertCell(0);
            const creditCell2 = creditRow.insertCell(1);
            const creditCell3 = creditRow.insertCell(2);
            const creditCell4 = creditRow.insertCell(3);
            const creditCell5 = creditRow.insertCell(4);
            const creditCell6 = creditRow.insertCell(5);
            const creditCell7 = creditRow.insertCell(6);
            const creditCell8 = creditRow.insertCell(7);

            creditCell1.textContent = '';
            creditCell2.textContent = '';
            creditCell3.textContent = '';
            creditCell4.textContent = '';
            creditCell5.textContent = creditAccount;
            creditCell6.textContent = '';
            creditCell7.textContent = creditAmount;
            creditCell8.textContent = '';

            document.getElementById('date').value = '';
            document.getElementById('voucher').value = '';
            document.getElementById('summary').value = '';
            document.getElementById('debit-account').value = '';
            document.getElementById('debit-amount').value = '';
            document.getElementById('credit-account').value = '';
            document.getElementById('credit-amount').value = '';

            calculateBalance();
            saveData();
        }

        function editEntry(button) {
            const row = button.parentNode.parentNode;
            const nextRow = row.nextElementSibling;
            const index = row.cells[0].textContent;
            document.getElementById('date').value = row.cells[1].textContent;
            document.getElementById('voucher').value = row.cells[2].textContent.replace(/^0+/, '');
            document.getElementById('summary').value = row.cells[3].textContent;
            document.getElementById('debit-account').value = row.cells[4].textContent;
            document.getElementById('debit-amount').value = row.cells[5].textContent;
            document.getElementById('credit-account').value = nextRow.cells[4].textContent;
            document.getElementById('credit-amount').value = nextRow.cells[6].textContent;
            // 删除原有分录
            table.deleteRow(nextRow.rowIndex);
            table.deleteRow(row.rowIndex);
        }

        function deleteEntry(button) {
            const row = button.parentNode.parentNode;
            const nextRow = row.nextElementSibling;
            // 删除两行分录
            table.deleteRow(nextRow.rowIndex);
            table.deleteRow(row.rowIndex);

            calculateBalance();
            saveData();
        }

        function calculateBalance() {
            const rows = table.getElementsByTagName('tr');
            let debitTotal = 0;
            let creditTotal = 0;

            for (let i = 1; i < rows.length; i++) {
                const debitCell = rows[i].cells[5];
                const creditCell = rows[i].cells[6];
                if (debitCell.textContent) {
                    debitTotal += parseFloat(debitCell.textContent);
                }
                if (creditCell.textContent) {
                    creditTotal += parseFloat(creditCell.textContent);
                }
            }

            if (debitTotal === creditTotal) {
                balanceInfo.textContent = `借贷平衡，借方总额: ${debitTotal.toFixed(2)}，贷方总额: ${creditTotal.toFixed(2)}`;
                balanceInfo.classList.remove('unbalanced');
                balanceInfo.classList.add('balanced');
            } else {
                balanceInfo.textContent = `借贷不平衡，借方总额: ${debitTotal.toFixed(2)}，贷方总额: ${creditTotal.toFixed(2)}`;
                balanceInfo.classList.remove('balanced');
                balanceInfo.classList.add('unbalanced');
            }
        }

        function exportToCSV() {
            const storedData = localStorage.getItem('accountingEntries');
            if (storedData) {
                const entries = JSON.parse(storedData);
                let csvContent = "序号,日期,凭证号,摘要,借方科目,借方金额,贷方科目,贷方金额\n";
                entries.forEach((entry) => {
                    csvContent += `${entry.index},${entry.date},${entry.voucher},${entry.summary},${entry.debitAccount},${entry.debitAmount},${entry.creditAccount},${entry.creditAmount}\n`;
                });

                const blob = new Blob([csvContent], { type: 'text/csv;charset=utf-8;' });
                const link = document.createElement('a');
                if (link.download !== undefined) {
                    const url = URL.createObjectURL(blob);
                    link.setAttribute('href', url);
                    link.setAttribute('download', 'accounting_entries.csv');
                    link.style.visibility = 'hidden';
                    document.body.appendChild(link);
                    link.click();
                    document.body.removeChild(link);
                }
            }
        }

        function importFromLocalStorage() {
            // 清空表格
            const tbody = table.getElementsByTagName('tbody')[0];
            while (tbody.firstChild) {
                tbody.removeChild(tbody.firstChild);
            }
            // 重新加载数据
            loadData();
        }

        function importFile() {
            const confirmImport = confirm('导入文件将覆盖原有数据，确认要继续吗？');
            if (confirmImport) {
                const fileInput = document.getElementById('file-input');
                const file = fileInput.files[0];
                if (file) {
                    // 清空表格
                    const tbody = table.getElementsByTagName('tbody')[0];
                    while (tbody.firstChild) {
                        tbody.removeChild(tbody.firstChild);
                    }

                    const reader = new FileReader();
                    reader.onload = function (e) {
                        const data = e.target.result;
                        let workbook;
                        const fragment = document.createDocumentFragment();
                        if (file.name.endsWith('.csv')) {
                            const csvData = data;
                            const lines = csvData.split('\n');
                            const headers = lines[0].split(',');
                            for (let i = 1; i < lines.length; i++) {
                                const line = lines[i].trim();
                                if (line) {
                                    const values = line.split(',');
                                    const entry = {};
                                    headers.forEach((header, index) => {
                                        entry[header.trim()] = values[index].trim();
                                    });
                                    // 处理日期和凭证号
                                    entry.日期 = formatDate(entry.日期);
                                    const debitRow = document.createElement('tr');
                                    const debitCell1 = debitRow.insertCell(0);
                                    const debitCell2 = debitRow.insertCell(1);
                                    const debitCell3 = debitRow.insertCell(2);
                                    const debitCell4 = debitRow.insertCell(3);
                                    const debitCell5 = debitRow.insertCell(4);
                                    const debitCell6 = debitRow.insertCell(5);
                                    const debitCell7 = debitRow.insertCell(6);
                                    const debitCell8 = debitRow.insertCell(7);

                                    debitCell1.textContent = entry.序号;
                                    debitCell2.textContent = entry.日期;
                                    debitCell3.textContent = padVoucher(entry.凭证号);
                                    debitCell4.textContent = entry.摘要;
                                    debitCell5.textContent = entry.借方科目;
                                    debitCell6.textContent = parseFloat(entry.借方金额).toFixed(2);
                                    debitCell7.textContent = '';
                                    debitCell8.innerHTML = '<button onclick="editEntry(this)">修改</button><button onclick="deleteEntry(this)">删除</button>';

                                    const creditRow = document.createElement('tr');
                                    const creditCell1 = creditRow.insertCell(0);
                                    const creditCell2 = creditRow.insertCell(1);
                                    const creditCell3 = creditRow.insertCell(2);
                                    const creditCell4 = creditRow.insertCell(3);
                                    const creditCell5 = creditRow.insertCell(4);
                                    const creditCell6 = creditRow.insertCell(5);
                                    const creditCell7 = creditRow.insertCell(6);
                                    const creditCell8 = creditRow.insertCell(7);

                                    creditCell1.textContent = '';
                                    creditCell2.textContent = '';
                                    creditCell3.textContent = '';
                                    creditCell4.textContent = '';
                                    creditCell5.textContent = entry.贷方科目;
                                    creditCell6.textContent = '';
                                    creditCell7.textContent = parseFloat(entry.贷方金额).toFixed(2);
                                    creditCell8.textContent = '';

                                    fragment.appendChild(debitRow);
                                    fragment.appendChild(creditRow);
                                }
                            }
                        } else if (file.name.endsWith('.xlsx')) {
                            workbook = XLSX.read(data, { type: 'binary' });
                            const firstSheetName = workbook.SheetNames[0];
                            const worksheet = workbook.Sheets[firstSheetName];
                            const jsonData = XLSX.utils.sheet_to_json(worksheet);
                            jsonData.forEach((entry) => {
                                // 处理日期和凭证号
                                entry.日期 = formatDate(entry.日期);
                                const debitRow = document.createElement('tr');
                                const debitCell1 = debitRow.insertCell(0);
                                const debitCell2 = debitRow.insertCell(1);
                                const debitCell3 = debitRow.insertCell(2);
                                const debitCell4 = debitRow.insertCell(3);
                                const debitCell5 = debitRow.insertCell(4);
                                const debitCell6 = debitRow.insertCell(5);
                                const debitCell7 = debitRow.insertCell(6);
                                const debitCell8 = debitRow.insertCell(7);

                                debitCell1.textContent = entry.序号;
                                debitCell2.textContent = entry.日期;
                                debitCell3.textContent = padVoucher(entry.凭证号);
                                debitCell4.textContent = entry.摘要;
                                debitCell5.textContent = entry.借方科目;
                                debitCell6.textContent = parseFloat(entry.借方金额).toFixed(2);
                                debitCell7.textContent = '';
                                debitCell8.innerHTML = '<button onclick="editEntry(this)">修改</button><button onclick="deleteEntry(this)">删除</button>';

                                const creditRow = document.createElement('tr');
                                const creditCell1 = creditRow.insertCell(0);
                                const creditCell2 = creditRow.insertCell(1);
                                const creditCell3 = creditRow.insertCell(2);
                                const creditCell4 = creditRow.insertCell(3);
                                const creditCell5 = creditRow.insertCell(4);
                                const creditCell6 = creditRow.insertCell(5);
                                const creditCell7 = creditRow.insertCell(6);
                                const creditCell8 = creditRow.insertCell(7);

                                creditCell1.textContent = '';
                                creditCell2.textContent = '';
                                creditCell3.textContent = '';
                                creditCell4.textContent = '';
                                creditCell5.textContent = entry.贷方科目;
                                creditCell6.textContent = '';
                                creditCell7.textContent = parseFloat(entry.贷方金额).toFixed(2);
                                creditCell8.textContent = '';

                                fragment.appendChild(debitRow);
                                fragment.appendChild(creditRow);
                            });
                        }
                        table.tBodies[0].appendChild(fragment);
                        calculateBalance();
                        saveData();
                    };

                    if (file.name.endsWith('.csv')) {
                        reader.readAsText(file);
                    } else if (file.name.endsWith('.xlsx')) {
                        reader.readAsBinaryString(file);
                    }
                }
            }
        }

        function insertEntry(entry) {
            const debitRow = table.insertRow();
            const debitCell1 = debitRow.insertCell(0);
            const debitCell2 = debitRow.insertCell(1);
            const debitCell3 = debitRow.insertCell(2);
            const debitCell4 = debitRow.insertCell(3);
            const debitCell5 = debitRow.insertCell(4);
            const debitCell6 = debitRow.insertCell(5);
            const debitCell7 = debitRow.insertCell(6);
            const debitCell8 = debitRow.insertCell(7);

            debitCell1.textContent = entry.序号;
            debitCell2.textContent = entry.日期;
            debitCell3.textContent = padVoucher(entry.凭证号);
            debitCell4.textContent = entry.摘要;
            debitCell5.textContent = entry.借方科目;
            debitCell6.textContent = parseFloat(entry.借方金额).toFixed(2);
            debitCell7.textContent = '';
            debitCell8.innerHTML = '<button onclick="editEntry(this)">修改</button><button onclick="deleteEntry(this)">删除</button>';

            const creditRow = table.insertRow();
            const creditCell1 = creditRow.insertCell(0);
            const creditCell2 = creditRow.insertCell(1);
            const creditCell3 = creditRow.insertCell(2);
            const creditCell4 = creditRow.insertCell(3);
            const creditCell5 = creditRow.insertCell(4);
            const creditCell6 = creditRow.insertCell(5);
            const creditCell7 = creditRow.insertCell(6);
            const creditCell8 = creditRow.insertCell(7);

            creditCell1.textContent = '';
            creditCell2.textContent = '';
            creditCell3.textContent = '';
            creditCell4.textContent = '';
            creditCell5.textContent = entry.贷方科目;
            creditCell6.textContent = '';
            creditCell7.textContent = parseFloat(entry.贷方金额).toFixed(2);
            creditCell8.textContent = '';
        }

        function formatDate(date) {
            if (typeof date === 'number') {
                // 处理 Excel 日期格式（数字），将 basetime 加一天
                const baseDate = new Date(1899, 11, 31);
                const millisecondsPerDay = 24 * 60 * 60 * 1000;
                const newDate = new Date(baseDate.getTime() + date * millisecondsPerDay);
                // 设置为东八区时间
                newDate.setUTCHours(8, 0, 0, 0);
                const year = newDate.getFullYear();
                const month = String(newDate.getMonth() + 1).padStart(2, '0');
                const day = String(newDate.getDate()).padStart(2, '0');
                return `${year}-${month}-${day}`;
            } else if (typeof date === 'string') {
                // 处理常见日期字符串格式
                const parsedDate = new Date(date);
                if (!isNaN(parsedDate.getTime())) {
                    // 设置为东八区时间
                    parsedDate.setUTCHours(8, 0, 0, 0);
                    const year = parsedDate.getFullYear();
                    const month = String(parsedDate.getMonth() + 1).padStart(2, '0');
                    const day = String(parsedDate.getDate()).padStart(2, '0');
                    return `${year}-${month}-${day}`;
                }
            }
            return date;
        }

        function padVoucher(voucher) {
            return String(voucher).padStart(3, '0');
        }

        // 页面加载时加载数据
        window.onload = function () {
            loadData();
        };
    </script>
</body>

</html>    